package com.yc.shopping.util;

import java.sql.*;
import java.util.*;
import java.io.*;

public class DBHelper {
	
	 // 驱动类路径
    protected static String driver;
    // 连接串
    protected static String url;
    // 用户名
    protected static String user;
    // 密码
    protected static String password;
    
    static {
    	try {
    		//初始化数据库配置参数
    		Properties prop = new Properties();//Map的子类
    		//获取类加载器
    		ClassLoader classLoader = DBHelper.class.getClassLoader();
    		//读取项目根目录的配置文件
    		InputStream in = classLoader.getResourceAsStream("jdbc.properties");
    		
    		if(in == null) {
    			throw new IOException("配置文件不存在!");
    		}
    		
    		prop.load(in);
    		driver = prop.getProperty("driver");
    		url = prop.getProperty("url");
    		user = prop.getProperty("user");
    		password = prop.getProperty("password");
    		//加载驱动
    		Class.forName(driver);
    		
    	}catch(IOException  e) {
    		throw new RuntimeException("数据库配置文件jdbc.properties 加载失败",e);
    	}catch (ClassNotFoundException e) {
			throw new RuntimeException("数据库驱动加载失败",e);
		}
    }
    public static void main(String[] args) throws SQLException {
    	//update("update dept set loc = ? where deptno = ?","衡阳",50);
    	List<Map<String,Object>> list = 
    			selectListMap("select * from emp where empno > ?",7788);
    	for(Map<String,Object> row : list)
    	System.out.println(row);
    	
//    	Map<String,Object> row = selectOneMap(
//    			"select * from emp where empno = ?", 7788);
//    	System.out.println(row);//正确
//    	row = selectOneMap("select * from emp where empno = ?",9999);
//    	System.out.println(row);
//    	try {
//    		row = selectOneMap("select * from emp where empno > ?",7788);
//    	}catch(Exception e) {
//    		e.printStackTrace();
//    		System.out.println("这是正确的处理方式,不能返回多行");
//    	}
    			
    }

    /**
     * 获取连接
     */
    public static Connection getConnection(){
        try {
        	//创建连接
        	return DriverManager.getConnection(url,user,password);
        }catch(Exception e) {
        	throw new RuntimeException(e);
        }
    }

    /**
     * 更新数据库
     */
    public static int update(String sql, Object... params) throws SQLException {
    	System.out.println("SQL:" + sql);
    	System.out.println("参数:" + Arrays.toString(params));
    	
    	try(Connection conn = getConnection();){
        	//构建语句
        	PreparedStatement ps = conn.prepareStatement(sql);
        	//设置参数
        	for(int i = 0;i < params.length;i++) {
        		ps.setObject(i + 1, params[i]);
        	}
        	//执行语句
        	return ps.executeUpdate();
        }
    }

    /**
     * 查询多行数据, 返回List<Map>集合
     */
    public static List<Map<String, Object>> selectListMap(String sql, Object... params) throws SQLException {
    	System.out.println("SQL:" + sql);
    	System.out.println("参数:" + Arrays.toString(params));
    	
    	try(Connection conn = getConnection();){
        	//构建语句
        	PreparedStatement ps = conn.prepareStatement(sql);
        	//设置参数
        	for(int i = 0; i < params.length;i++) {
        		ps.setObject(i + 1, params[i]);
        	}
        	ResultSet rs = ps.executeQuery();
        	//返回结果集"元数据"对象
        	ResultSetMetaData rsmd = rs.getMetaData();
        	//返回结果集中列的数量
        	int columnCount = rsmd.getColumnCount();
        	//创建返回的集合对象
        	List<Map<String,Object>> list = new ArrayList<>();
        	while(rs.next()) {
        		//创建Map集合
        		Map<String,Object> row = new LinkedHashMap<>();
        		for(int i = 0;i < columnCount; i++){
        			String columnName = rsmd.getColumnName(i+1);
        			Object columnValue = rs.getObject(i + 1);
        			row.put(columnName, columnValue);
        		}
        		//将该行数据对象存入list集合
        		list.add(row);
        	}
        	return list;
        }
    }

    /**
     * 查询单行数据, 返回Map集合
     */
    public static Map<String, Object> selectOneMap(String sql, Object... params) throws SQLException {
    	System.out.println("SQL:" + sql);
    	System.out.println("参数:" + Arrays.toString(params));
    	
    	try(Connection conn = getConnection();){
        	//构建语句
        	PreparedStatement ps = conn.prepareStatement(
        			sql,
        			ResultSet.TYPE_SCROLL_INSENSITIVE,	//设置可滚动结果集
        			ResultSet.CONCUR_READ_ONLY);		//只读结果集
        	//设置参数
        	for(int i = 0 ;i < params.length; i++) {
        		ps.setObject(i+1, params[i]);
        	}
        	ResultSet rs = ps.executeQuery();
        	// 返回结果集"元数据"对象
        	ResultSetMetaData rsmd = rs.getMetaData();
        	//返回结果集中列的数量
        	int columnCount = rsmd.getColumnCount();
        	//判断结果集数量是否正确
        	if(rs.next() == false) {
        		//结果集中没有数据，返回null
        		return null;
        	}
        	
        	//再向下走一行
        	if(rs.next() ) {
        		// IllegalStateException 表示状态不正确的异常
        		throw new IllegalStateException("结果集数量大于1");
        	}
        	
        	// 控制游标回走一行,默认结果集不能回走
        	rs.previous();
        	
        	//创建返回的集合对象
        	Map<String,Object> row = new LinkedHashMap<>();
        	for(int i = 0; i < columnCount; i++) {
        		String columnName = rsmd.getColumnName(i+1);
        		Object columnValue = rs.getObject(i+1);
        		row.put(columnName, columnValue);
        	}
        	return row;
        }
    }

    /**
     * 查询多行数据, 返回List<T>集合, T 为实体类对象
     */
    public static <T> List<T> selectListBean(String sql, Class<T> cls, Object... params) throws SQLException {
        return null;
    }

    /**
     * 查询单行数据, 返回T对象, T 为实体类对象
     */
    public static <T> T selectOneBean(String sql, Class<T> cls, Object... params) throws SQLException {
        return null;
    }

    /**
     * 查询分页数据
     */
    public static Page<Map<String, Object>> selectPage(String sql, int page, int size, Object... params) throws SQLException {
        
    	//分页结果需要两个数据
    	//1.当前页结果集
    	//2.单行数
    	
    	//构建分页对象
    	Page<Map<String,Object>> ret = new Page(page,size);
    	
    	String pageSql = "select a.*\n" +
    					"  from (select a.*, rownum rn from ("+ sql +") a where rownum <= ?) a\n" + 
    					" where a.rn > ?";
    	int begin = (page - 1)*size;
    	int end = begin + size;
    	
    	Object[] newParams = new Object[params.length + 2];
    	//复制参数数组
    	System.arraycopy(params, 0, newParams, 0, params.length);
    	
    	//赋值分页参数
    	newParams[params.length] = end;
    	newParams[params.length + 1] = begin;
    	
    	List<Map<String,Object>> data = DBHelper.selectListMap(pageSql, newParams);
    	
    	//查询不分页的单行数，用于计算单页数
    	String countsql = "select count(*) cnt from ("+ sql +")";
    	Map<String,Object> map = DBHelper.selectOneMap(countsql, params);
    	int rows = Integer.parseInt("" + map.get("CNT")); // map.get("CNT") ==> java.math.BigDecimal or Long
    	
    	//设置到分页对象中
    	ret.setData(data, rows);
    	return ret;
    	
    }

    /**
     * 事务模板方法
     */
    public static <T> T execute(SqlHandler handler) {
    	
    	Connection conn = getConnection();
    	try {
    		conn.setAutoCommit(false);
    		handler.handle(conn);
    		//如果成功则提交
    		conn.commit();
    	}catch(Exception e) {
    		try {
    			conn.rollback();//出问题则回滚
    		}catch(SQLException e1) {
    			throw new RuntimeException("事物回滚失败",e1);
    		}
    		e.printStackTrace();
    	}finally {
    		try {
    			conn.close();//关闭资源
    		}catch(SQLException e) {
    			throw new RuntimeException("关闭连接失败",e);
    		}
    	}
        return null;
    }

    /**
     * 执行SQL语句的 函数接口
     */
    public static interface SqlHandler<T> {
        T handle(Connection connection);
    }

    public static class Page<E> {

        /**
         * 当前页数
         */
        private int page = 1;
        /**
         * 每页行数
         */
        private int size = 10;
        /**
         * 结果集总记录数
         */
        private int rows;
        /**
         * 结果集总页数
         */
        private int pages;
        /**
         * 查询到当前页的数据
         */
        private List<E> data;

        public Page(int page, int size) {
            this.page = page;
            this.size = size;
        }

        public void setData(List<E> data, int rows) {
            this.data = data;
            this.rows = rows;
            // 计算总页数
            this.pages = rows / size;
            pages += rows % size == 0 ? 0 : 1;
        }

        /**
         * 返回前后页的页码
         *
         * @return
         */
        public int nextPage() {
            return page == pages ? page : (page + 1);
        }

        /**
         * 返回前一页的页码
         *
         * @return
         */
        public int priorPage() {
            return page == 1 ? page : (page - 1);
        }

        public int getPage() {
            return page;
        }

        public int getSize() {
            return size;
        }

        public int getRows() {
            return rows;
        }

        public int getPages() {
            return pages;
        }

        public List<E> getData() {
            return data;
        }
    }
}
